﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.Sql;

using Rmlife.Common;
using Rmlife.Model;
using Rmlife.Config;
using Rmlife.Data;
namespace Rmlife.Site
{
    public class Bookmarks
    {
        #region IDataProvider 实现  Bookmarks网摘
        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="pagesize"></param>
        /// <param name="pagesize"></param>
        /// <param name="condition"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public static DataTable GetBookMarkList(int pageindex, int pagesize, string condition, string order)
        {
            string topselect = "";
            if (pagesize > 0)
            {
                topselect = " top " + pagesize + " ";
            }
            if (pageindex <= 0)
                pageindex = 1;

            if (!string.IsNullOrEmpty(order.Trim()))
            {
                order = " order by " + order;
            }

            //所需字段
            string field = "BookmarkID,BookmarkUrl,Title,ThumbnailUrl,Body,PrivacyStatus,AuditingStatus,SiteCategoryID,UserID,Author,DateCreated,UserCount,HitTimes,DayHitTimes,WeekHitTimes,CommentCount,BackerCount,DissenterCount";
            string table = " dbo.spb_Bookmarks";
            string keyfield = "BookmarkID";
            string sql = "";
            if (pageindex == 1)
            {
                sql = "select " + topselect + " " + field + " FROM" + table + " WHERE 1=1 " + condition + "" + order;
                
            }
            else
            {
                sql = "select top " + pagesize.ToString() 
                    + " " + field + " FROM " + table 
                    + " WHERE " + keyfield + " not in (select top " + ((pageindex - 1) * pagesize).ToString() 
                    + " " + keyfield + " from " + table 
                    + " WHERE 1=1 " + condition + " " + order + ")" + condition + " " + order;
            }
            return DbHelper.ExecuteDataset(sql).Tables[0];
        }

        /// <summary>
        /// 得到一条记录
        /// </summary>
        /// <param name="BookmarkID"></param>
        /// <returns></returns>
        public static BookmarkInfo GetBookMarkInfo(int BookmarkID)
        {
            BookmarkInfo info = null;
            DbParameter[] parm = {
                                 DbHelper.MakeInParam("@BookmarkID",(DbType)SqlDbType.Int,4,BookmarkID)
                                 };
            string sql = "select * from dbo.spb_Bookmarks where BookmarkID=@BookmarkID";
            SqlDataReader dr = (SqlDataReader)DbHelper.ExecuteReader(CommandType.Text, sql, parm);
            if (dr.Read())
            {
                info = new BookmarkInfo();
                info.BookmarkID = dr["BookmarkID"] != null && !string.IsNullOrEmpty(dr["BookmarkID"].ToString()) ? int.Parse(dr["BookmarkID"].ToString()) : 0;
                info.BookmarkUrl = dr["BookmarkUrl"] != null && !string.IsNullOrEmpty(dr["BookmarkUrl"].ToString()) ? dr["BookmarkUrl"].ToString() : "";
                info.Title = dr["Title"] != null && !string.IsNullOrEmpty(dr["Title"].ToString()) ? dr["Title"].ToString() : "";
                info.ThumbnailUrl = dr["ThumbnailUrl"] != null && !string.IsNullOrEmpty(dr["ThumbnailUrl"].ToString()) ? dr["ThumbnailUrl"].ToString() : "";
                info.Body = dr["Body"] != null && !string.IsNullOrEmpty(dr["Body"].ToString()) ? dr["Body"].ToString() : "";
                info.PrivacyStatus = dr["PrivacyStatus"] != null && !string.IsNullOrEmpty(dr["PrivacyStatus"].ToString()) ? int.Parse(dr["PrivacyStatus"].ToString()) : 0;
                info.AuditingStatus = dr["AuditingStatus"] != null && !string.IsNullOrEmpty(dr["AuditingStatus"].ToString()) ? int.Parse(dr["AuditingStatus"].ToString()) : 0;
                info.SiteCategoryID = dr["SiteCategoryID"] != null && !string.IsNullOrEmpty(dr["SiteCategoryID"].ToString()) ? int.Parse(dr["SiteCategoryID"].ToString()) : 0;
                info.UserID = dr["UserID"] != null && !string.IsNullOrEmpty(dr["UserID"].ToString()) ? int.Parse(dr["UserID"].ToString()) : 0;
                info.Author = dr["Author"] != null && !string.IsNullOrEmpty(dr["UserID"].ToString()) ? dr["UserID"].ToString() : "";
                info.DateCreated = dr["DateCreated"] != null && !string.IsNullOrEmpty(dr["DateCreated"].ToString()) ? Convert.ToDateTime(dr["DateCreated"].ToString()) : DateTime.Parse("1900-1-1 0:00:00");
                info.UserCount = dr["UserCount"] != null && !string.IsNullOrEmpty(dr["UserCount"].ToString()) ? int.Parse(dr["UserCount"].ToString()) : 0;
                info.HitTimes = dr["HitTimes"] != null && !string.IsNullOrEmpty(dr["HitTimes"].ToString()) ? int.Parse(dr["HitTimes"].ToString()) : 0;
                info.DayHitTimes = dr["DayHitTimes"] != null && !string.IsNullOrEmpty(dr["DayHitTimes"].ToString()) ? int.Parse(dr["DayHitTimes"].ToString()) : 0;
                info.WeekHitTimes = dr["WeekHitTimes"] != null && !string.IsNullOrEmpty(dr["WeekHitTimes"].ToString()) ? int.Parse(dr["WeekHitTimes"].ToString()) : 0;
                info.CommentCount = dr["CommentCount"] != null && !string.IsNullOrEmpty(dr["CommentCount"].ToString()) ? int.Parse(dr["CommentCount"].ToString()) : 0;
                info.BackerCount = dr["BackerCount"] != null && !string.IsNullOrEmpty(dr["BackerCount"].ToString()) ? int.Parse(dr["BackerCount"].ToString()) : 0;
                info.DissenterCount = dr["DissenterCount"] != null && !string.IsNullOrEmpty(dr["DissenterCount"].ToString()) ? int.Parse(dr["DissenterCount"].ToString()) : 0;

            }
            dr.Close();
            return info;
        }

        public static bool ModifyBookmarkInfo(BookmarkInfo info)
        {
            DbParameter[] parms = { DbHelper.MakeInParam("@BookmarkID", (DbType)SqlDbType.Int, 4, info.BookmarkID),
                                  DbHelper.MakeInParam("@BookmarkUrl",(DbType)SqlDbType.NVarChar,512,info.BookmarkUrl),
                                  DbHelper.MakeInParam("@Title",(DbType)SqlDbType.NVarChar,256,info.Title),
                                  DbHelper.MakeInParam("@ThumbnailUrl",(DbType)SqlDbType.NVarChar,512,info.ThumbnailUrl),
                                  DbHelper.MakeInParam("@Body",(DbType)SqlDbType.NText,3000,info.Body),
                                  DbHelper.MakeInParam("@PrivacyStatus",(DbType)SqlDbType.Int,4,info.PrivacyStatus),
                                  DbHelper.MakeInParam("@SiteCategoryID",(DbType)SqlDbType.Int,4,info.SiteCategoryID),
                                  DbHelper.MakeInParam("@AuditingStatus",(DbType)SqlDbType.Int,4,info.AuditingStatus),
                                  DbHelper.MakeInParam("@UserID",(DbType)SqlDbType.Int,4,info.UserID),
                                  DbHelper.MakeInParam("@Author",(DbType)SqlDbType.NVarChar,64,info.Author),
                                  DbHelper.MakeInParam("@DateCreated",(DbType)SqlDbType.DateTime,8,info.DateCreated),
                                  DbHelper.MakeInParam("@UserCount",(DbType)SqlDbType.Int,4,info.UserCount),
                                  DbHelper.MakeInParam("@HitTimes",(DbType)SqlDbType.Int,4,info.HitTimes),
                                  DbHelper.MakeInParam("@DayHitTimes",(DbType)SqlDbType.Int,4,info.DayHitTimes),
                                  DbHelper.MakeInParam("@WeekHitTimes",(DbType)SqlDbType.Int,4,info.WeekHitTimes),
                                  DbHelper.MakeInParam("@CommentCount",(DbType)SqlDbType.Int,4,info.CommentCount),
                                  DbHelper.MakeInParam("@BackerCount",(DbType)SqlDbType.Int,4,info.BackerCount),
                                  DbHelper.MakeInParam("@DissenterCount",(DbType)SqlDbType.Int,4,info.DissenterCount)
                                  };
            string sql = "update spb_Bookmarks set BookmarkUrl=@BookmarkUrl,Title=@Title,ThumbnailUrl=@ThumbnailUrl,Body=@Body,PrivacyStatus=@PrivacyStatus,AuditingStatus=@AuditingStatus,SiteCategoryID=@SiteCategoryID,UserID=@UserID,Author=@Author,DateCreated=@DateCreated,UserCount=@UserCount,HitTimes=@HitTimes,DayHitTimes=@DayHitTimes,WeekHitTimes=@WeekHitTimes,CommentCount=@CommentCount,BackerCount=@BackerCount,DissenterCount=@DissenterCount where BookmarkID=@BookmarkID ";
            return DbHelper.ExecuteNonQuery(CommandType.Text, sql, parms) > 0;
        }


        public static bool AddBookmarkInfo(BookmarkInfo info)
        {
            DbParameter[] parms = {
                                  DbHelper.MakeInParam("@BookmarkUrl",(DbType)SqlDbType.NVarChar,512,info.BookmarkUrl),
                                  DbHelper.MakeInParam("@Title",(DbType)SqlDbType.NVarChar,256,info.Title),
                                  DbHelper.MakeInParam("@ThumbnailUrl",(DbType)SqlDbType.NVarChar,512,info.ThumbnailUrl),
                                  DbHelper.MakeInParam("@Body",(DbType)SqlDbType.NText,3000,info.Body),
                                  DbHelper.MakeInParam("@PrivacyStatus",(DbType)SqlDbType.Int,4,info.PrivacyStatus),
                                  DbHelper.MakeInParam("@SiteCategoryID",(DbType)SqlDbType.Int,4,info.SiteCategoryID),
                                  DbHelper.MakeInParam("@AuditingStatus",(DbType)SqlDbType.Int,4,info.AuditingStatus),
                                  DbHelper.MakeInParam("@UserID",(DbType)SqlDbType.Int,4,info.UserID),
                                  DbHelper.MakeInParam("@Author",(DbType)SqlDbType.NVarChar,64,info.Author),
                                  DbHelper.MakeInParam("@DateCreated",(DbType)SqlDbType.DateTime,8,info.DateCreated),
                                  DbHelper.MakeInParam("@UserCount",(DbType)SqlDbType.Int,4,info.UserCount),
                                  DbHelper.MakeInParam("@HitTimes",(DbType)SqlDbType.Int,4,info.HitTimes),
                                  DbHelper.MakeInParam("@DayHitTimes",(DbType)SqlDbType.Int,4,info.DayHitTimes),
                                  DbHelper.MakeInParam("@WeekHitTimes",(DbType)SqlDbType.Int,4,info.WeekHitTimes),
                                  DbHelper.MakeInParam("@CommentCount",(DbType)SqlDbType.Int,4,info.CommentCount),
                                  DbHelper.MakeInParam("@BackerCount",(DbType)SqlDbType.Int,4,info.BackerCount),
                                  DbHelper.MakeInParam("@DissenterCount",(DbType)SqlDbType.Int,4,info.DissenterCount)
                                  };
            string sql = "insert into spb_Bookmarks(BookmarkUrl,Title,ThumbnailUrl,Body,PrivacyStatus,AuditingStatus,SiteCategoryID,UserID,Author,DateCreated,UserCount,HitTimes,DayHitTimes,WeekHitTimes,CommentCount,BackerCount,DissenterCount) values(@BookmarkUrl,@Title,@ThumbnailUrl,@Body,@PrivacyStatus,@AuditingStatus,@SiteCategoryID,@UserID,@Author,@DateCreated,@UserCount,@HitTimes,@DayHitTimes,@WeekHitTimes,@CommentCount,@BackerCount,@DissenterCount)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, sql, parms) > 0;
        }

        public static bool DelBookmarkInfo(int MarkID)
        {
            DbParameter[] parms = { DbHelper.MakeInParam("@MarkID", (DbType)SqlDbType.Int, 4, MarkID) };

            string sql = "delete dbo.spb_Bookmarks where BookmarkID=@MarkID";
            return DbHelper.ExecuteNonQuery(CommandType.Text, sql, parms) > 0;
        }

        public static int QueryBookmarkCnt(string strWhere)
        {
            string sql = "select count(1) as icount from dbo.spb_Bookmarks where 1=1" + strWhere;
            SqlDataReader dr = (SqlDataReader)DbHelper.ExecuteReader(CommandType.Text,sql);
            return dr.Read() ? Utils.StrToInt(dr["icount"].ToString()) : 0;
        }
        /// <summary>
        /// 检测此数据是否存在
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public static int ExistByID(int ID)
        {
            DbParameter[] parms = {DbHelper.MakeInParam("@MarkID",(DbType)SqlDbType.Int,4,ID) };
            string sql = "select count(1) as icount from dbo.spb_Bookmarks where BookmarkID=@MarkID";
            SqlDataReader red = (SqlDataReader)DbHelper.ExecuteReader(CommandType.Text, sql, parms);
            return red.Read() ? Utils.StrToInt(red["icount"].ToString()) : 0;
        }

        public static bool UpdateBookmarkInfo(int ID)
        {
            DbParameter[] parms = { DbHelper.MakeInParam("@BookmarkID", (DbType)SqlDbType.Int, 4, ID),
                                  };
            string sql = "update spb_Bookmarks set HitTimes=HitTimes+1,DayHitTimes=@DayHitTimes,WeekHitTimes=WeekHitTimes+1,CommentCount=CommentCount+1,BackerCount=BackerCount+1,DissenterCount=DissenterCount+1 where BookmarkID=@BookmarkID ";
            return DbHelper.ExecuteNonQuery(CommandType.Text, sql, parms) > 0;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="BookmarkUrl"></param>
        /// <param name="Title"></param>
        /// <param name="Author"></param>
        /// <returns></returns>
        public static bool UpdUrlByID(int ID, string BookmarkUrl, string Title, string Author)
        {
            DbParameter[] parms = { DbHelper.MakeInParam("@BookmarkID", (DbType)SqlDbType.Int, 4, ID),
                                      DbHelper.MakeInParam("@BookmarkUrl", (DbType)SqlDbType.NVarChar,512, BookmarkUrl),
                                      DbHelper.MakeInParam("@Title", (DbType)SqlDbType.NVarChar,256, Title),
                                       DbHelper.MakeInParam("@Author", (DbType)SqlDbType.NVarChar,64, Author)
                                  };
            string sql = "update spb_Bookmarks set BookmarkUrl=@BookmarkUrl,Title=@Title,Author=@Author where BookmarkID=@BookmarkID ";
            return DbHelper.ExecuteNonQuery(CommandType.Text, sql, parms) > 0;
        }

        #endregion
    }
}
